clear all
set more off

global rawdata "data directory here" 
global output "output directory here"

clear
cd $rawdata
import delimited using ce1996.csv
 
rename v1 year
rename v2 geo
rename v3 org
rename v4 ftype
rename v5 est_year 
rename v7 ind
rename v8 self_m
rename v9 self_f 
rename v18 totalw_m
rename v19 totalw_f
 
keep if (ind >= 930 & ind < 940) | (ind >= 520 & ind < 530)


gen ne = self_f + self_m

rename year yr
gen firmage = yr-est_year
replace firmage =0 if firmage <0
replace firmage =. if firmage >200 

gen femaleSE = 0  
replace femaleSE = 1 if org == 1 & ne > 0 & ne == self_f
gen maleSE = 0  
replace maleSE = 1 if org == 1 & ne > 0 & ne == self_m

gen newfirmall = 0 
replace newfirmall = 1 if firmage == 0  
gen newfirm = 0 
replace newfirm = 1 if newfirmall == 1 & org == 1
gen newfirmf = 0 
replace newfirmf = 1 if newfirm == 1 & femaleSE == 1 
gen newfirmm = 0 
replace newfirmm = 1 if newfirm == 1 & maleSE == 1 

bys yr geo: egen Fnew_service = total(newfirmf) 
bys yr geo: egen Mnew_service = total(newfirmm) 
bys yr geo: gen tag = _n
keep if tag == 1 

keep yr geo Fnew_service Mnew_service ind
cd $output
save service1996,replace





clear
cd $rawdata
import delimited using ce1997.csv


rename v1 year
rename v2 geo
rename v3 org
rename v4 ftype
rename v5 est_year 
rename v7 ind
rename v8 self_m
rename v9 self_f 
rename v18 totalw_m
rename v19 totalw_f
 
keep if (ind >= 930 & ind < 940) | (ind >= 520 & ind < 530)



gen ne = self_f + self_m

rename year yr
gen firmage = yr-est_year
replace firmage =0 if firmage <0
replace firmage =. if firmage >200 

gen femaleSE = 0  
replace femaleSE = 1 if org == 1 & ne > 0 & ne == self_f
gen maleSE = 0  
replace maleSE = 1 if org == 1 & ne > 0 & ne == self_m

gen newfirmall = 0 
replace newfirmall = 1 if firmage == 0  
gen newfirm = 0 
replace newfirm = 1 if newfirmall == 1 & org == 1
gen newfirmf = 0 
replace newfirmf = 1 if newfirm == 1 & femaleSE == 1 
gen newfirmm = 0 
replace newfirmm = 1 if newfirm == 1 & maleSE == 1 

bys yr geo: egen Fnew_service = total(newfirmf) 
bys yr geo: egen Mnew_service = total(newfirmm) 
bys yr geo: gen tag = _n
keep if tag == 1 

keep yr geo Fnew_service Mnew_service ind

cd $output
save service1997,replace




clear
cd $rawdata
import delimited using ce1998.csv
 
rename v1 year
rename v2 geo
rename v3 org
rename v4 ftype
rename v5 est_year 
rename v8 ind
rename v9 self_m
rename v10 self_f 
rename v19 totalw_m
rename v20 totalw_f
 
keep if (ind >= 930 & ind < 940) | (ind >= 520 & ind < 530)

gen ne = self_f + self_m

rename year yr
gen firmage = yr-est_year
replace firmage =0 if firmage <0
replace firmage =. if firmage >200 

gen femaleSE = 0  
replace femaleSE = 1 if org == 1 & ne > 0 & ne == self_f
gen maleSE = 0  
replace maleSE = 1 if org == 1 & ne > 0 & ne == self_m

gen newfirmall = 0 
replace newfirmall = 1 if firmage == 0  
gen newfirm = 0 
replace newfirm = 1 if newfirmall == 1 & org == 1
gen newfirmf = 0 
replace newfirmf = 1 if newfirm == 1 & femaleSE == 1 
gen newfirmm = 0 
replace newfirmm = 1 if newfirm == 1 & maleSE == 1 

bys yr geo: egen Fnew_service = total(newfirmf) 
bys yr geo: egen Mnew_service = total(newfirmm) 
bys yr geo: gen tag = _n
keep if tag == 1 

keep yr geo Fnew_service Mnew_service ind

cd $output
save service1998,replace




clear
cd $rawdata
import delimited using ce1999.csv
 
rename v1 year
rename v2 geo
rename v3 org
rename v4 ftype
rename v5 est_year 
rename v8 ind
rename v9 self_m
rename v10 self_f 
rename v19 totalw_m
rename v20 totalw_f
 
keep if (ind >= 930 & ind < 940) | (ind >= 520 & ind < 530)

gen ne = self_f + self_m

rename year yr
gen firmage = yr-est_year
replace firmage =0 if firmage <0
replace firmage =. if firmage >200 

gen femaleSE = 0  
replace femaleSE = 1 if org == 1 & ne > 0 & ne == self_f
gen maleSE = 0  
replace maleSE = 1 if org == 1 & ne > 0 & ne == self_m

gen newfirmall = 0 
replace newfirmall = 1 if firmage == 0  
gen newfirm = 0 
replace newfirm = 1 if newfirmall == 1 & org == 1
gen newfirmf = 0 
replace newfirmf = 1 if newfirm == 1 & femaleSE == 1 
gen newfirmm = 0 
replace newfirmm = 1 if newfirm == 1 & maleSE == 1 

bys yr geo: egen Fnew_service = total(newfirmf) 
bys yr geo: egen Mnew_service = total(newfirmm) 
bys yr geo: gen tag = _n
keep if tag == 1 

keep yr geo Fnew_service Mnew_service ind


cd $output
save service1999,replace

 

forvalues j = 0/5{
clear
cd $rawdata
import delimited using ce200`j'.csv, varn(nonames) numericcols(_all)
drop if _n == 1
forvalues i =1/8{
replace v`i'=0 if v`i'== .
}

forvalues i =10/28{
replace v`i'=0 if v`i'== .
}
 
rename v1 year
rename v2 geo
rename v3 id
rename v4 ceo_gender
rename v5 est_year 
rename v7 org
rename v8 ftype
rename v10 ind
rename v12 self_m
rename v13 self_f 
rename v27 totalw_m
rename v28 totalw_f
 
keep if (ind >= 930 & ind < 940) | (ind >= 520 & ind < 530)

gen ne = self_f + self_m

rename year yr
gen firmage = yr-est_year
replace firmage =0 if firmage <0
replace firmage =. if firmage >200 

gen femaleSE = 0  
replace femaleSE = 1 if org == 1 & ne > 0 & ne == self_f
gen maleSE = 0  
replace maleSE = 1 if org == 1 & ne > 0 & ne == self_m

gen newfirmall = 0 
replace newfirmall = 1 if firmage == 0  
gen newfirm = 0 
replace newfirm = 1 if newfirmall == 1 & org == 1
gen newfirmf = 0 
replace newfirmf = 1 if newfirm == 1 & femaleSE == 1 
gen newfirmm = 0 
replace newfirmm = 1 if newfirm == 1 & maleSE == 1 

bys yr geo: egen Fnew_service = total(newfirmf) 
bys yr geo: egen Mnew_service = total(newfirmm) 
bys yr geo: gen tag = _n
keep if tag == 1 

keep yr geo Fnew_service Mnew_service ind
cd $output
save service200`j',replace
}


clear
cd $output
use service1996
forvalue i = 1997/2005 {
append using service`i', force
}  
rename ind ind_coe
save service_all, replace

  
